package com.goldcn.goods.service;

import com.aliyun.oss.OSSClient;
import com.goldcn.common.OSSManageUtil;
import com.goldcn.common.PropertyUtil;
import com.goldcn.common.util.UuidUtils;
import com.goldcn.config.OSSConfigure;
import com.goldcn.enumeration.OperateTypeEnum;
import com.goldcn.goods.model.GoodsSku;
import com.goldcn.goods.vo.GoodsDetailsVo;
import com.goldcn.goods.vo.GoodsInfoVo;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExcelImageService {

    private Logger logger = Logger.getLogger(ExcelImageService.class);

    @Autowired
    private OSSConfigure ossConfigure;

    @Autowired
    private GoodsService goodsService;

    /**
     * 注意事项： 名称严格匹配，要跟云管控的后台一致
     * 蓝宝石属性
     * table_field_fieldType
     * fieldType: txt, list, integer,decimal
     * goods.txt为字段，list表示属性json
     */
    //private final static String[] skuKeys = {"goods_prdNo_txt", "goods_imgUrl_txt", "goods_hxStore_list_所在库", "sku_hxCutType_list_切工类型", "sku_hxColor_list_颜色", "goods_hxGradeNO_list_级别色号", "sku_hxShape_list_形状", "sku_hxSize_list_尺寸", "goods_hxHandleMethod_list_处理方法", "sku_hxNeatness_list_净度", "sku_hxLucency_list_透明度", "sku_hxCutGrade_list_切工级别", "sku_hxFireGrade_list_火彩级别", "sku_msWeight_decimal_重量", "sku_wholeSalePrice_decimal", "sku_price_decimal", "sku_inventory_integer", "goods_goodsPCDesc_txt"};

    private final static String[] skuKeys = {"goods_prdNo_txt", "goods_imgUrl_txt", "goods_companyName_list_公司名称", "goods_klPrice_list_克拉单价", "goods_dlPrice_list_单粒价格", "goods_nativeGem_list_宝石种类", "goods_hxOrigin_list_产地", "goods_hxCutType_list_车工类型", "sku_hxColor_list_颜色级别", "sku_hxShape_list_形状", "sku_hxSize_list_尺寸规格", "goods_gemGrade_list_宝石级别", "goods_hxCutGrade_list_切工级别", "goods_hxLucency_list_透明度级别", "goods_hxFireGrade_list_火彩级别", "sku_hxNeatness_list_净度级别", "goods_hxHandleMethod_list_处理方法", "goods_hxKind_list_种类", "goods_hxSingleNum_list_粒数", "sku_weight_decimal_重量", "sku_wholeSalePrice_decimal", "sku_price_decimal", "sku_inventory_integer", "goods_memo_list_备注"};


    /**
     * 钻石属性
     */
    private final static String[] skuKeys_diamond = {"sku_hxShape_list_形状", "sku_msWeight_decimal_重量", "sku_msColor_list_颜色", "sku_msClarity_list_净度", "sku_msCut_list_切工", "sku_hxPolish_list_抛光", "sku_hxSym_list_对称性", "sku_hxLight_list_荧光性", "goods_GGcertOrg_list_证书鉴定机构", "sku_GGcertNum_txt_证书", "sku_wholeSalePrice_decimal", "sku_price_decimal", "sku_inventory_integer"};


    private final static Map<String, String> para = new HashMap<String, String>();

    private final static String FTYPE_TXT = "txt";
    private final static String FTYPE_LIST = "list";
    private final static String FTYPE_INTEGER = "integer";
    private final static String FTYPE_DECIMAL = "decimal";

    static {
        //生产
        para.put("hxOrigin", "qNIkfC0JQ5ONbHsS4YYIXg");
        para.put("hxCutType", "S52UgHoSISCdviUaLi9eQ");
        para.put("hxNeatness", "KhBBpH0RFKGUsjK3rPQaw");
        para.put("hxHandleMethod", "GrlWtWWJS_eZNRHoDBs64w");
        para.put("hxFireGrade", "2_s9kakTWqQ05q56M074A");
        para.put("hxLucency", "pFX7fKRSGOCiAEt_jAy-Q");
        para.put("nativeGem", "JQUs_ZcaRPuvFEt8qdJ0yA");
        para.put("hxCutGrade", "gt-96HfTF6qTF-XIUhHtA");
        para.put("companyName", "7NRC39LBS8aYLmtvyh9MwA");
        para.put("klPrice", "vjHcOy8rSwSGUqxnejeZ6g");
        para.put("dlPrice", "wZVFrSkSQ-iY_N4_uBoEjQ");
        para.put("memo", "FcXw72b0TM6LF5lzKLU-9Q");
        para.put("hxSingleNum", "cIM3qyZ5QfauYrPV_MJkEA");
        para.put("hxKind", "bh0B2VB1Q7iJ3t3J1fokjQ");
        para.put("gemGrade", "zMjpeYf2QVG303fn08bWIQ");
    }


    private final static int BEGIN_ROW = 1;
    private final static int BEGIN_COLUMN = 1;


    private final static int BEGIN_ROW_HX = 1;
    private final static int BEGIN_COLUMN_HX = 0;


    /**
     * 导入蓝帝裸石
     *
     * @param fileName
     * @param goodsInfoVo
     * @return
     * @throws Exception
     */
    public Object importXlsLD(String fileName, GoodsInfoVo goodsInfoVo) throws Exception {

        Long begin = System.currentTimeMillis();

        Map message = new HashMap();
        String ftpPath = ossConfigure.getFtpDir();
        InputStream inputStream = new FileInputStream(ftpPath + File.separator + fileName);
        //InputStream inputStream = new FileInputStream("f:/1.xls");

        HSSFWorkbook workbook = (HSSFWorkbook) WorkbookFactory.create(inputStream);
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);
        Map<String, Object> map = new HashMap<String, Object>();

        //图片预处理
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;
                int pictureIndex = pic.getPictureIndex() - 1;
                HSSFPictureData picData = pictures.get(pictureIndex);
                map.put(pictureIndex + "", picData);
            }
        }

        OSSClient ossClient = null;
        String imageName = "";

        Map<String, String> xlsImgList = new HashMap<String, String>();
        try {
            ossClient = OSSManageUtil.getOSSClient(ossConfigure);
            for (Map.Entry me : map.entrySet()) {
                HSSFPictureData picData = (HSSFPictureData) me.getValue();
                imageName = UuidUtils.uuid() + ".png";
                imageName = OSSManageUtil.uploadFile(ossClient, ossConfigure.getBucketName(), picData.getData(), imageName);
                //savePic(me.getKey() + "", picData);
                xlsImgList.put(me.getKey() + "", imageName);
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("xls图片上传失败", e);
        } finally {
            OSSManageUtil.closeOSSClient(ossClient);
        }
        logger.info("导入图片ok！数量====" + map.size());


        GoodsDetailsVo goodsDetailsVo = new GoodsDetailsVo();
        goodsDetailsVo.setOperateType(OperateTypeEnum.insert);
        List<String> imageUrlList = new ArrayList<String>();
        List<GoodsSku> goodsSkuList = new ArrayList<>();
        List<JSONObject> goodsAttrList = new ArrayList<>();

        StringBuffer skuName = new StringBuffer();

        String key = "";
        Object cellValue = null;
        String[] temp, stemp = null;
        String field, fieldType, fieldName, tableName = null;

        //遍历数据
        int i = 0;
        for (Row row : sheet) {
            if (row.getRowNum() < BEGIN_ROW) {
                continue;
            }
            //skuJSON
            JSONObject jsonObject = new JSONObject();
            GoodsSku goodsSku = new GoodsSku();

            for (Cell cell : row) {
                if (cell.getColumnIndex() < BEGIN_COLUMN) {
                    continue;
                }
                key = null;
                cellValue = null;
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        key = skuKeys[cell.getColumnIndex() - 1];
                        cellValue = StringUtils.trim(cell.getRichStringCellValue().getString());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        key = skuKeys[cell.getColumnIndex() - 1];
                        cellValue = cell.getNumericCellValue();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        break;
                    default:
                }
                if (key != null) {
                    temp = key.split("_");
                    field = temp[1];
                    fieldType = temp[2];
                    tableName = temp[0];
                    if (tableName.equalsIgnoreCase("goods")) {
                        //产品信息
                        if (fieldType.equalsIgnoreCase(FTYPE_TXT)) {
                            PropertyUtil.setValue(goodsInfoVo, field, cellValue);
                        } else {
                            if (cellValue instanceof String) {
                                //goodsJson
                                JSONObject jsonObjectGoods = new JSONObject();

                                fieldName = temp[3];
                                stemp = ((String) cellValue).split(" ");
                                if (stemp.length > 1 && !field.equalsIgnoreCase("memo")) {
                                    cellValue = stemp[stemp.length - 1];
                                }
                                //找回属性id
                                if (para.get(field) != null) {
                                    jsonObjectGoods.put("cfadid", para.get(field));
                                } else {
                                    jsonObjectGoods.put("cfadid", stemp[0]);
                                }
                                jsonObjectGoods.put("clazzattrcode", field);
                                jsonObjectGoods.put("clazzattrval", cellValue);
                                jsonObjectGoods.put("clazzattrname", fieldName);
                                goodsAttrList.add(jsonObjectGoods);
                            } else if (cellValue instanceof Number) {

                                JSONObject jsonObjectGoods = new JSONObject();
                                fieldName = temp[3];
                                //找回属性id
                                if (para.get(field) != null) {
                                    jsonObjectGoods.put("cfadid", para.get(field));
                                } else {
                                    jsonObjectGoods.put("cfadid", stemp[0]);
                                }
                                jsonObjectGoods.put("clazzattrcode", field);
                                jsonObjectGoods.put("clazzattrval", cellValue);
                                jsonObjectGoods.put("clazzattrname", fieldName);
                                goodsAttrList.add(jsonObjectGoods);


                            }
                        }
                    } else if (tableName.equalsIgnoreCase("sku")) {
                        //产品sku信息
                        if (cellValue instanceof String) {
                            fieldName = temp[3];
                            stemp = ((String) cellValue).split(" ");
                            if (stemp.length > 1) {
                                cellValue = stemp[stemp.length - 1];
                            }
                            PropertyUtil.setValue(goodsSku, field, cellValue);
                            jsonObject.put(field, cellValue);
                            skuName.append(fieldName + ":" + cellValue + "+");
                        } else if (cellValue instanceof Number) {

                            if (fieldType.equalsIgnoreCase(FTYPE_INTEGER)) {
                                PropertyUtil.setValue(goodsSku, field, new BigDecimal(cellValue.toString()).intValue());
                            } else if (fieldType.equalsIgnoreCase(FTYPE_DECIMAL)) {
                                PropertyUtil.setValue(goodsSku, field, new BigDecimal(cellValue.toString()));
                            } else if (fieldType.equalsIgnoreCase(FTYPE_LIST)) {
                                fieldName = temp[3];
                                jsonObject.put(field, cellValue);
                                skuName.append(fieldName + ":" + cellValue + "+");
                            }

                            //重量单独处理
                            if (field.equalsIgnoreCase("weight")) {
                                fieldName = temp[3];
                                jsonObject.put(field, cellValue);
                                skuName.append(fieldName + ":" + cellValue + "+");
                            }
                        }
                    }
                }
            }

            if (goodsAttrList.size() > 0) {
                goodsInfoVo.setAttr(goodsAttrList.toString());
            } else {
                goodsInfoVo.setAttr("[]");
            }

            goodsInfoVo.setGoodsAPPDesc("<p>" + goodsInfoVo.getGoodsPCDesc() + "<br/></p>");
            goodsInfoVo.setGoodsPCDesc("<html><head lang=\\\"en\\\"><meta name=\\\"viewport\\\" content=\\\"width=device-width,initial-scale=1,maximum-scale=1,user-scalable=0\\\"><meta content=\\\"telephone=no\\\" name=\\\"format-detection\\\"><meta name=\\\"apple-mobile-web-app-capable\\\" content=\\\"yes\\\"><meta content=\\\"\\\" name=\\\"keywords\\\"><meta content=\\\"\\\" name=\\\"description\\\"><meta name=\\\"viewport\\\" content=\\\"width=device-width,initial-scale=1,user-scalable=0,minimum-scale=1,maximum-scale=1\\\"><meta name=\\\"apple-mobile-web-app-capable\\\" content=\\\"yes\\\"><meta name=\\\"apple-mobile-web-app-status-bar-style\\\" content=\\\"black\\\"><style type=\\\"text/css\\\">* {margin: 0;padding: 0}body {position: relative;font: 14px \\\"microsoft yahei\\\";}</style></head><body><p>" + goodsInfoVo.getGoodsPCDesc() + "<br/></p></body></html>");

            if (skuName.toString().length() > 1) {
                jsonObject.put("name", skuName.toString().substring(0, skuName.toString().length() - 1));
            } else {
                jsonObject.put("name", skuName.toString());
            }
            goodsSku.setAttrJson(jsonObject.toString());
            goodsSkuList.add(goodsSku);
            goodsDetailsVo.setGoodsSkuList(goodsSkuList);

            goodsDetailsVo.setGoodsInfoVo(goodsInfoVo);

            if (i < xlsImgList.size()) {
                imageUrlList.add(xlsImgList.get(i + ""));
                goodsDetailsVo.setImageUrlList(imageUrlList);
                try {
                    goodsService.operateGoods(goodsDetailsVo);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            //清除数据
            imageUrlList.clear();
            skuName.setLength(0);
            goodsSkuList.clear();
            goodsAttrList.clear();

            i++;
        }
        try {
            inputStream.close();
            File folderFile = new File(ftpPath + File.separator + fileName);
            if (folderFile.isFile()) {
                folderFile.delete();
                folderFile = null;
            }
        } catch (Exception ex) {
            logger.error(ex);
        } finally {
            inputStream = null;
        }
        message.put("success", "从xls成功导入(" + i + ")条记录,总共耗时" + (System.currentTimeMillis() - begin) + "毫秒");
        return message;

    }


    /**
     * 导入恒信钻石
     *
     * @param fileName
     * @param goodsInfoVo
     * @return
     * @throws Exception
     */
    public Object importXlsHX(String fileName, GoodsInfoVo goodsInfoVo) throws Exception {

        Long begin = System.currentTimeMillis();

        Map message = new HashMap();
        String ftpPath = ossConfigure.getFtpDir();
        InputStream inputStream = new FileInputStream(ftpPath + File.separator + fileName);
        //InputStream inputStream = new FileInputStream("f:/3.xls");

        HSSFWorkbook workbook = (HSSFWorkbook) WorkbookFactory.create(inputStream);
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);

        Map<String, Object> map = new HashMap<String, Object>();

        //图片预处理
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;
                int pictureIndex = pic.getPictureIndex() - 1;
                HSSFPictureData picData = pictures.get(pictureIndex);
                map.put(pictureIndex + "", picData);
            }
        }

        OSSClient ossClient = null;
        String imageName = "";

        Map<String, String> xlsImgList = new HashMap<String, String>();
        try {
            ossClient = OSSManageUtil.getOSSClient(ossConfigure);
            for (Map.Entry me : map.entrySet()) {
                HSSFPictureData picData = (HSSFPictureData) me.getValue();
                imageName = UuidUtils.uuid() + ".png";
                imageName = OSSManageUtil.uploadFile(ossClient, ossConfigure.getBucketName(), picData.getData(), imageName);
                //savePic(me.getKey() + "", picData);
                xlsImgList.put(me.getKey() + "", imageName);
            }
        } catch (Exception e) {
            logger.info(e.getMessage());
        } finally {
            OSSManageUtil.closeOSSClient(ossClient);
        }

        logger.info("导入图片ok！数量====" + map.size());

        GoodsDetailsVo goodsDetailsVo = new GoodsDetailsVo();
        goodsDetailsVo.setOperateType(OperateTypeEnum.insert);

        List<GoodsSku> goodsSkuList = new ArrayList<>();
        List<String> imageUrlList = new ArrayList<>();
        List<JSONObject> goodsAttrList = new ArrayList<>();

        StringBuffer skuName = new StringBuffer();

        String key = "";
        Object cellValue = null;
        String[] temp, stemp = null;
        String field, fieldType, fieldName, tableName = null;

        //遍历数据
        int i = 0;
        for (Row row : sheet) {
            if (row.getRowNum() < BEGIN_ROW_HX) {
                continue;
            }
            //skuJSON
            JSONObject jsonObject = new JSONObject();
            GoodsSku goodsSku = new GoodsSku();

            for (Cell cell : row) {
                if (cell.getColumnIndex() < BEGIN_COLUMN_HX) {
                    continue;
                }
                key = null;
                cellValue = null;
                /*证书号  读取数字单元格内容转为文本类型 */
                if (cell.getColumnIndex() == 10) {
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                }
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        key = skuKeys_diamond[cell.getColumnIndex() - 1];
                        cellValue = StringUtils.trim(cell.getRichStringCellValue().getString());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        key = skuKeys_diamond[cell.getColumnIndex() - 1];
                        cellValue = cell.getNumericCellValue();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        break;
                    default:
                }
                if (key != null) {
                    temp = key.split("_");
                    field = temp[1];
                    fieldType = temp[2];
                    tableName = temp[0];
                    if (tableName.equalsIgnoreCase("goods")) {

                        //产品信息
                        if (fieldType.equalsIgnoreCase(FTYPE_TXT)) {
                            PropertyUtil.setValue(goodsInfoVo, field, cellValue);
                        } else {
                            if (cellValue instanceof String) {
                                //goodsJson
                                JSONObject jsonObjectGoods = new JSONObject();

                                fieldName = temp[3];
                                stemp = ((String) cellValue).split(" ");
                                if (stemp.length > 1) {
                                    cellValue = stemp[stemp.length - 1];
                                }
                                //找回属性id
                                if (para.get(field) != null) {
                                    jsonObjectGoods.put("cfadid", para.get(field));
                                } else {
                                    jsonObjectGoods.put("cfadid", stemp[0]);
                                }
                                jsonObjectGoods.put("clazzattrcode", field);
                                jsonObjectGoods.put("clazzattrval", cellValue);
                                jsonObjectGoods.put("clazzattrname", fieldName);
                                goodsAttrList.add(jsonObjectGoods);
                            }
                        }
                    } else if (tableName.equalsIgnoreCase("sku")) {
                        //产品sku信息
                        if (cellValue instanceof String) {
                            fieldName = temp[3];
                            stemp = ((String) cellValue).split(" ");
                            if (stemp.length > 1) {
                                cellValue = stemp[stemp.length - 1];
                            }
                            PropertyUtil.setValue(goodsSku, field, cellValue);
                            jsonObject.put(field, cellValue);
                            skuName.append(fieldName + ":" + cellValue + "+");
                            //证书号作为款号
                            if (field.equalsIgnoreCase("GGcertNum")) {
                                goodsInfoVo.setPrdNo(cellValue.toString());
                                goodsInfoVo.setGoodsPCDesc(cellValue.toString());
                            }
                        } else if (cellValue instanceof Number) {
                            if (fieldType.equalsIgnoreCase(FTYPE_INTEGER)) {
                                PropertyUtil.setValue(goodsSku, field, new BigDecimal(cellValue.toString()).intValue());
                            } else if (fieldType.equalsIgnoreCase(FTYPE_DECIMAL)) {
                                PropertyUtil.setValue(goodsSku, field, new BigDecimal(cellValue.toString()));
                            } else if (fieldType.equalsIgnoreCase(FTYPE_LIST)) {
                                fieldName = temp[3];
                                jsonObject.put(field, cellValue);
                                skuName.append(fieldName + ":" + cellValue + "+");
                            }
                            //重量单独处理
                            if (field.equalsIgnoreCase("msWeight")) {
                                fieldName = temp[3];
                                jsonObject.put(field, cellValue);
                                skuName.append(fieldName + ":" + cellValue + "+");
                            }
                        }
                    }
                }
            }

            if (goodsAttrList.size() > 0) {
                goodsInfoVo.setAttr(goodsAttrList.toString());
            } else {
                goodsInfoVo.setAttr("[]");
            }

            goodsInfoVo.setGoodsAPPDesc("<p>" + goodsInfoVo.getGoodsPCDesc() + "<br/></p>");
            goodsInfoVo.setGoodsPCDesc("<html><head lang=\\\"en\\\"><meta name=\\\"viewport\\\" content=\\\"width=device-width,initial-scale=1,maximum-scale=1,user-scalable=0\\\"><meta content=\\\"telephone=no\\\" name=\\\"format-detection\\\"><meta name=\\\"apple-mobile-web-app-capable\\\" content=\\\"yes\\\"><meta content=\\\"\\\" name=\\\"keywords\\\"><meta content=\\\"\\\" name=\\\"description\\\"><meta name=\\\"viewport\\\" content=\\\"width=device-width,initial-scale=1,user-scalable=0,minimum-scale=1,maximum-scale=1\\\"><meta name=\\\"apple-mobile-web-app-capable\\\" content=\\\"yes\\\"><meta name=\\\"apple-mobile-web-app-status-bar-style\\\" content=\\\"black\\\"><style type=\\\"text/css\\\">* {margin: 0;padding: 0}body {position: relative;font: 14px \\\"microsoft yahei\\\";}</style></head><body><p>" + goodsInfoVo.getGoodsPCDesc() + "<br/></p></body></html>");

            if (skuName.toString().length() > 1) {
                jsonObject.put("name", skuName.toString().substring(0, skuName.toString().length() - 1));
            } else {
                jsonObject.put("name", skuName.toString());
            }
            goodsSku.setAttrJson(jsonObject.toString());
            goodsSkuList.add(goodsSku);
            goodsDetailsVo.setGoodsSkuList(goodsSkuList);

            goodsDetailsVo.setGoodsInfoVo(goodsInfoVo);


            if (xlsImgList.get(i + "") != null) {
                imageUrlList.add(xlsImgList.get(i + ""));
            } else {
                imageUrlList.add(xlsImgList.get("0"));
            }
            goodsDetailsVo.setImageUrlList(imageUrlList);

            //保存数据
            goodsService.operateGoods(goodsDetailsVo);

            //清除数据
            imageUrlList.clear();
            skuName.setLength(0);
            goodsSkuList.clear();
            goodsAttrList.clear();

            i++;
        }
        try {
            inputStream.close();
            File folderFile = new File(ftpPath + File.separator + fileName);
            if (folderFile.isFile()) {
                folderFile.delete();
                folderFile = null;
            }
        } catch (Exception ex) {
            logger.error(ex);
        } finally {
            inputStream = null;
        }
        message.put("success", "从xls成功导入(" + i + ")条记录,总共耗时" + (System.currentTimeMillis() - begin) + "毫秒");
        return message;

    }

    private static void savePic(String i, PictureData pic) throws Exception {
        String ext = pic.suggestFileExtension();
        byte[] data = pic.getData();
        if (ext.equalsIgnoreCase("png") || ext.equalsIgnoreCase("jpg") || ext.equalsIgnoreCase("jpeg")) {
            FileOutputStream out = new FileOutputStream("F:\\" + i + ".jpg");
            out.write(data);
            out.close();
        }

    }


    public static void main1(String[] ds) throws Exception {

        ExcelImageService xx = new ExcelImageService();
        xx.importXlsHX(null, new GoodsInfoVo());

    }

    public static void main2(String[] ds) throws Exception {

        InputStream inputStream = new FileInputStream("f://1.xls");
        HSSFWorkbook workbook = (HSSFWorkbook) WorkbookFactory.create(inputStream);
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);
        Map<String, Object> map = new HashMap<String, Object>();


        //图片预处理
        int i = 1;
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;
                int row = anchor.getRow2();
                int col = anchor.getCol2();
                int pictureIndex = pic.getPictureIndex() - 1;
                HSSFPictureData picData = pictures.get(pictureIndex);

                //map.put(row + ":" + col, picData);
                savePic(pictureIndex + "", picData);

                i++;
            }
        }

    }


}
